Java database FAQ: How do I connect to a SQL database with Java and JDBC?
If you’re interested in connecting your Java applications to standard SQL databases like Oracle, MySQL, SQL Server, and others, JDBC is what you need. Java’s JDBC technology lets you access information in SQL databases using standard SQL queries.
JDBC connections
This article shows a step-by-step example of how to establish a database connection from your Java programs to a SQL database using JDBC (i.e., creating a “JDBC connection”). In the process I’ll show you how to connect to two different databases — Mini SQL (mSQL), and Interbase — just so you can see how the code changes when you switch from one database to another.
Note: I was going to update this tutorial to connect to MySQL and Postgres, but the databases you connect to don’t matter much; that’s one of the beauties of JDBC, it makes the database you’re connecting to almost transparent. Once you’re connected to the database with JDBC, most SQL queries are identical, or at least very similar.
Obtaining the JDBC driver
Before you start working with JDBC, you'll need a copy of the Java JDK. If you don't have it already, you can get the JDK/SDK for free at Oracle's Java web site, or it will also be included with many IDEs that you can use, including Eclipse, IntelliJ IDEA, and NetBeans.
Once you have the JDK, the next thing you need to do is to get the correct JDBC driver for your database. The documentation for the database you’re using will usually tell you where you can download the JDBC driver for its database, but to make it easier, I've created a page of JDBC drivers for common SQL databases.
Once you have the correct JDBC driver for your database, install it according to the instructions that came with it. Installation instructions will vary somewhat for each vendor, but it’s usually as easy as putting a JAR file in the lib folder of your Java project.
Create a JDBC database connection in two steps
Once you have the correct JDBC driver installed, establishing a JDBC connection from your Java programs to your SQL database is pretty easy.
Regardless of whether you're trying to connect to Oracle, SQL Server, MySQL, Postgres, mSQL, or Interbase (or any other JDBC data source), establishing a connection to a SQL database with JDBC is a simple two-step process:
- Load the JDBC driver.
- Establish the JDBC connection to your database.
The following two examples will show how easy this is, and how little the code changes when you migrate from one database server to another.
1) A Java JDBC Connection example
Here's the full source code required to establish a JDBC connection to a mSQL database running on a server named dbserver.com:
// Establish a connection to a mSQL database using JDBC
import java.sql.*;
class JdbcTest1 {
public static void main (String[] args) {
try
{
// Step 1: "Load" the JDBC driver
Class.forName("com.imaginary.sql.msql.MsqlDriver");
// Step 2: Establish the connection to the database
String url = "jdbc:msql://dbserver.com:1114/contact_mgr";
Connection conn = DriverManager.getConnection(url,"user1","password");
}
catch (Exception e)
{
System.err.println("D'oh! Got an exception!");
System.err.println(e.getMessage());
}
}
}
As you can see, connecting to a database requires just these two steps. Assuming that these steps work successfully, the variable conn is a connection to your database, and you can use that connection to SELECT, INSERT, and DELETE data in your database tables, as I’ll show in future tutorials.
2) An Interbase JDBC ODBC Connection example
To show how consistent this process is when you use different databases, this source code shows how to connect to an Interbase database that’s running on the local computer:
// Establish a connection to an Interbase database using JDBC and ODBC.
import java.sql.*;
class JdbcTest1
{
public static void main (String[] args)
{
try
{
// Step 1: Load the JDBC ODBC driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Step 2: Establish the connection to the database
String url = "jdbc:odbc:contact_mgr";
Connection conn = DriverManager.getConnection(url,"user1","password");
}
catch (Exception e)
{
System.err.println("Got an exception! ");
System.err.println(e.getMessage());
}
}
}
As you can see, the text inside the forName and getConnection methods is different, but the rest of your Java code is the same.
What's the difference?
More specifically as you can see from the code, there are two differences in the code:
- The name of the JDBC driver.
- The JDBC URL used to connect to the database.
Everything else in the two source code listings -- except for the comment at the top -- is identical. Here's a slightly more detailed discussion of the two differences:
1.The JDBC Driver
The name of the JDBC driver will be supplied to you by your database vendor. As you can see in the class.forName() statements, these names will vary. In the first case we're using the mSQL-JDBC driver. In the second case we're using the JDBC-ODBC Bridge driver supplied with the Interbase server.
2. The URL
The syntax of the DriverManager.getConnection() method is:
DriverManager.getConnection(String url, String username, String password);
The username and password are the normal names you use to log into your database. The URL you use will again vary with the database you use. In both examples shown, we're establishing a connection to a database named contact_mgr. (We'll use this database for all of our examples in this series of JDBC articles.)
If you stick with standard SQL commands, it can be very easy to switch from one database server to another. In fact, I've heard from several developers who are using mSQL to prototype their software (because it's so inexpensive), and then switching to another commercial vendor when it's time to take their product "live".
For your reference, I just created this page of example JDBC connection strings (JDBC driver string and URL).
What's next
Now that you know how to connect to a database using Java & JDBC, you’ll probably want to read one of these two tutorials:
- How to INSERT data into a SQL database using Java/JDBC
- How to SELECT data from a SQL database using Java/JDBC
In summary, I hope this series of Java/JDBC tutorials is helpful.
books by alvin
 |
 |
 |
 |